﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; 
using U9API.Pub;
using System.Data;
using U9API.JsonModel;
using Newtonsoft.Json;
using System.Reflection;

namespace U9API.APIHelper
{
    public class SalePriceList
    {
        public Int64 ID { get; set; }
        public string Code { get; set; } 
        public string Name { get; set; }
        public string Currency { get; set; }
        public bool IsIncludeTax { get; set; }
         

        public List<SalePriceLine> SalePriceLines { get; set; }
    }

    public class SalePriceLine
    {
        public Int64 ID { get; set; }
        public Int64 ItemInfo_ItemID { get; set; } 
        public Int64 Uom { get; set; }
        public decimal Price { get; set; }
        public string FromDate { get; set; }
        public string ToDate { get; set; } 
    }
    public class SalePriceHelper : BaseHelper<JsonModel.ApiQuery>
    { 
        public List<U9API.DBHelper.Model.Customer> Resdatas { get; set; }
        public string result;


        private SalePriceList DataRowToHModel(DataRow row)
        {
            SalePriceList model = new SalePriceList();
            Type examType = typeof(SalePriceList);
            BindingFlags flag = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
            if (row != null)
            {
                DataColumnCollection dc = row.Table.Columns;
                for (int i = 0; i < dc.Count; i++)
                {
                    if (row[dc[i].ColumnName] != null && row[dc[i].ColumnName].ToString() != "")
                    {
                        PropertyInfo piInstance = examType.GetProperty(dc[i].ColumnName, flag);
                        if (piInstance != null)
                        {
                            piInstance.SetValue(model, row[dc[i].ColumnName], null);
                        }
                    }
                }
            }

            return model;
        }

        private SalePriceLine DataRowToBModel(DataRow row)
        {
            SalePriceLine model = new SalePriceLine();

            Type examType = typeof(SalePriceLine);
            BindingFlags flag = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
            if (row != null)
            {
                DataColumnCollection dc = row.Table.Columns;
                for (int i = 0; i < dc.Count; i++)
                {
                    if (row[dc[i].ColumnName] != null && row[dc[i].ColumnName].ToString() != "")
                    {
                        PropertyInfo piInstance = examType.GetProperty(dc[i].ColumnName, flag);
                        if (piInstance != null)
                        {
                            piInstance.SetValue(model, row[dc[i].ColumnName], null);
                        }
                    }
                }
            }
            return model;
        }

        protected override void Create(string U9constr, int operation, List<JsonModel.ApiQuery> models )
        {
            //Pub.WriteLog.WriteLine(DateTime.Now.ToShortDateString() + ": SalePriceHelper");
            if (models.Count == 0)
            {
                throw new Exception("查询条件不允许为空!");
            }
            JsonModel.ApiQuery model = models[0];
            StringBuilder sql = new StringBuilder();
            sql.Append("select a.ID, a.code, c.name, d.name as Currency, a.IsIncludeTax ");
            sql.Append(" from SPR_SalePriceList a ");
            sql.Append(" inner join SPR_SalePriceList_Trl c on c.ID = a.ID ");
            sql.Append(" left join Base_Currency_Trl d on d.id = a.Currency and  d.SysMLFlag= 'zh-CN'");
            sql.Append(" where c.SysMLFlag = 'zh-CN' and a.Status = 2 and a.Cancel_Canceled = 0 ");
           
            if (!string.IsNullOrEmpty(model.code))
            {
                sql.Append("and a.code = '" + model.code + "'");
            }
            if (!string.IsNullOrEmpty(model.id))
            {
                sql.Append("and a.ID = '" + model.id + "'");
            } 
            if (!string.IsNullOrEmpty(model.startdate))
            {
                sql.Append(" and (a.CreatedOn >='" + model.startdate + "' or a.ModifiedOn >='" + model.startdate +"')");
            }
            if (!string.IsNullOrEmpty(model.enddate))
            {
                sql.Append(" and (a.CreatedOn <='" + model.enddate + "' or a.ModifiedOn <='" + model.enddate + "')");
            }
            DataSet ds = DbHelperSQL.Query(sql.ToString(), U9constr);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                List<SalePriceList> mlist = new List<SalePriceList>();
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    mlist.Add(DataRowToHModel(ds.Tables[0].Rows[i]));
                }

                foreach (var item in mlist)
                {
                    StringBuilder sqlb = new StringBuilder();
                    sqlb.Append("select * from (select b.id,  b.ItemInfo_ItemID, b.price, b.Uom,  Convert(varchar(10), b.FromDate,121) as  FromDate, Convert(varchar(10),  b.ToDate,121) as ToDate, ");
                    sqlb.Append(" ROW_NUMBER() over (partition by ItemInfo_ItemID order by b.fromdate desc) as irowno ");
                    sqlb.Append(" from  SPR_SalePriceLine b where b.active = 1 and b.SalePriceList = " + item.ID);
                    sqlb.Append(" ) b where b.irowno = 1 ");

                    DataTable dtb = DbHelperSQL.QueryAsDT(sqlb.ToString(), U9constr);
                    if (dtb != null && dtb.Rows.Count > 0)
                    {
                        item.SalePriceLines = new List<SalePriceLine>();
                        for (int i = 0; i < dtb.Rows.Count; i++)
                        {
                            item.SalePriceLines.Add(DataRowToBModel(dtb.Rows[i]));
                        }
                    }
                }
                result = mlist.ToJson(); 
            }
            else
            {
                throw new Exception("没有让到匹配的价目表记录!");
                
            }
        }
         
    }
}
